package oracle;

import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.* ;

public class Jdbc_oracle {
    private static final Log log = LogFactory.get();
    //orcl为oracle数据库中的数据库名，localhost表示连接本机的oracle数据库     
    //1521为连接的端口号     
    //private static final String url="jdbc:oracle:thin:@192.168.0.100:1521:ZX";
    private static final String url="jdbc:oracle:thin:@test.bjdev.com.cn:6521:zx";
    //system为登陆oracle数据库的用户名     
    private static final String user="ZX";
    //manager为用户名system的密码     
    private static final String password="zx";
    public static Connection conn;
    public static PreparedStatement ps;
    public static ResultSet rs;
    public static Statement st ;
    public static Statement stLoc ;
    public static ResultSet rsLoc;
    //连接数据库的方法     
    public void getConnection(){
        try {
            //初始化驱动包     
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //根据数据库连接字符，名称，密码给conn赋值     
            conn=DriverManager.getConnection(url, user, password);

        } catch (Exception e) {
            // TODO: handle exception     
            e.printStackTrace();
        }
    }
    //测试能否与oracle数据库连接成功     
    public static void main(String[] args) {
        Jdbc_oracle basedao=new Jdbc_oracle();
        basedao.getConnection();
        if(conn==null){
            System.out.println("与oracle远程数据库连接失败！");
        }else{
            System.out.println("与oracle远程数据库连接成功！");
        }
        JdbcLocalhost jdbcLocalhost = new JdbcLocalhost();
        Connection connection = jdbcLocalhost.getConnection();
        if(connection == null){
            System.out.println("与oracle本地数据库连接失败！");
        }else{
            System.out.println("与oracle本地数据库连接成功！");
        }
        String sql = "select * from PICTURE_CT where rownum<=1";     // 查询数据的sql语句
        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);    //执行sql查询语句，返回查询数据的结果集
            log.info("数据",rs);
            System.out.println("最后的查询结果为：");//创建用于执行静态sql语句的Statement对象，st属局部变量
            while (rs.next()) { // 判断是否还有下一个数据
                PictureCt pictureCt = new PictureCt();
                // 根据字段名获取相应的值     
                pictureCt.setCid(rs.getString("CID"));
                pictureCt.setCh(rs.getString("CH"));
                pictureCt.setRq(rs.getDate("RQ"));
                pictureCt.setDwdm(rs.getString("DWDM"));
                pictureCt.setBw(rs.getString("BW"));
                pictureCt.setZp(rs.getBytes("ZP"));
                pictureCt.setId(rs.getString("ID"));
                System.out.println(pictureCt.getCh());
                stLoc = connection.createStatement();
                //拼接SQL语句
                String sqlLoc = "INSERT INTO PICTURE_CT (CID,CH,RQ,DWDM,BW,ZP,ID ) VALUES ("+pictureCt.getCid()+","+pictureCt.getCh()+","+pictureCt.getRq()+","+pictureCt.getDwdm()+","+pictureCt.getBw()+","+pictureCt.getZp()+","+pictureCt.getId()+")";
                int rsLoc = stLoc.executeUpdate(sqlLoc);
                System.out.println(rsLoc);
            }
            conn.close();   //关闭数据库连接 
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }

    }
}
